--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

/*CREATE TABLE se_met_indivart AS (SELECT * FROM &erp.met_indivart WHERE 1=2);*/

/*PROMPT CREATE INDEX se_met_indivart_01
CREATE INDEX se_met_indivart_01
  ON se_met_indivart (
    client_cod,
    individu_cod,
    refe_cod,
    refe_ind
  )
  NOLOGGING
; */






DELETE FROM se_met_indivart;

DROP TABLE temp_combadv_tailindiv1;

CREATE TABLE temp_combadv_tailindiv1 AS (
SELECT v.id,v.CODVAR,a.ATTMSQ,val.VALVAR
FROM UV_CINDIVART_CODVAR v, UV_CINDIVART_ATTMSQ a,UV_CINDIVART_VALVAR val
WHERE a.id=v.id AND a.RANGMULTI2=v.RANGMULTI2
  AND val.id=a.id AND val.RANGMULTI2=a.RANGMULTI2 AND val.RANGMULTI3=a.RANGMULTI3 ) ;
/*
SELECT * FROM temp_combadv_tailindiv1 WHERE id = '9018300070575823511580'*/
 /*supprime les .. inutile avant les valeur entre "" */
UPDATE temp_combadv_tailindiv1 SET VALVAR=REGEXP_REPLACE(VALVAR,'^(\.{1,})(.{1,})','\2')  ;
 /*supprime les .. inutile apr�s les valeur entre "" */
UPDATE temp_combadv_tailindiv1 SET VALVAR=REGEXP_REPLACE(VALVAR,'(.{1,})(\.{1,})$','\1');

UPDATE temp_combadv_tailindiv1 SET VALVAR='STD' WHERE valvar='.' AND codvar='LON'  ;

/*remise a plat des longueur et taille par indivart*/
DROP TABLE temp_combadv_tailindiv2;
CREATE TABLE temp_combadv_tailindiv2 AS (
SELECT u.id,Max(Decode(codvar||attmsq,'LON1',valvar,NULL)) LON1
           ,Max(Decode(codvar||attmsq,'LON2',valvar,NULL)) LON2
           ,Max(Decode(codvar||attmsq,'TAI1',valvar,NULL)) TAI1
           ,Max(Decode(codvar||attmsq,'TAI2',valvar,NULL)) TAI2
FROM temp_combadv_tailindiv1 u
GROUP BY u.id) ;

/*remise a plat des valeur de variantes des combadv pour l'article TAILINDIV*/
DROP TABLE temp_combadv_tailindiv3;
CREATE TABLE temp_combadv_tailindiv3 AS (
SELECT v.combadv_id,Max(Decode(vrt_cod||att_cod,'LON1',att_val,NULL)) LON1
                   ,Max(Decode(vrt_cod||att_cod,'LON2',att_val,NULL)) LON2
                   ,Max(Decode(vrt_cod||att_cod,'TAI1',att_val,NULL)) TAI1
                   ,Max(Decode(vrt_cod||att_cod,'TAI2',att_val,NULL)) TAI2
FROM &erp.vrt_vcombadvval v
WHERE v.gene_cod(+)='TAILINDIV' AND v.gene_ind(+)='0'
GROUP BY v.combadv_id ) ;

/*lien entre les longueurs et tailles et les combadv_id de l'article TAILINDIV*/
DROP TABLE temp_combadv_tailindiv4;
CREATE TABLE temp_combadv_tailindiv4 AS (
SELECT uv.*,se.combadv_id
FROM temp_combadv_tailindiv2 uv,temp_combadv_tailindiv3 se
WHERE uv.lon1=se.lon1(+)
  AND uv.lon2=se.lon2(+)
  AND uv.tai1=se.tai1(+)
  AND uv.tai2=se.tai2(+) ) ;

SELECT DISTINCT 'REPRISE/TAILINDIV.TXT' outname, 'TAILINDIV' "ART_COMP.GENE_COD",'0' "ART_COMP.GENE_IND",/*"ART_COMP.COMBSTO_ID",*/
        'MLI+LON+1+'||LON1||']MLI+LON+2+'||LON2||']MLI+TAI+1+'||TAI1||']MLI+TAI+2+'|| TAI2
        || CASE WHEN TAI2='TPHS' THEN 'MLI+THS+1+]MLI+THS+2+]MLI+THS+3+]MLI+THS+4+]MLI+THS+5+]MLI+THS+6+]MLI+THS+7+]MLI+THS+8+]MLI+THS+9+]MLI+THS+10+]MLI+THS+10+' ELSE '' end LISTEVAR_LIB
FROM terp_mli2.temp_combadv_tailindiv4 WHERE combadv_id IS NULL ;



 INSERT INTO se_met_indivart
 (SELECT i.client client_cod,
         i.longu individu_cod,
         SubStr(m.ID,0,Length(m.ID)-1) refe_cod,
         m.indice refe_ind,
         0 enreg_id,
         Nvl(l.combadv_id ,0) combadv_id,
         'TAILINDIV' gene_cod,
         '0' gene_ind
  FROM UV_CINDIVART ia, UV_CINDIVIDUS i, UV_cmodeles m, uv_cclients c ,temp_combadv_tailindiv4 l
  WHERE i.id=ia.individu
    AND m.id=ia.article
    AND c.id=i.client
    AND l.id=ia.id
) ;

/*affecte la combadv_id du generique � d�faut d'avoir trouv� la bonne*/
 UPDATE se_met_indivart SET combadv_id = (SELECT COMBADV_ID FROM &erp.vrt_combadv WHERE gene_cod ='TAILINDIV' AND  COMBADV_TYP ='S' )
 WHERE  combadv_id =0 ;



 UPDATE se_met_indivart tf SET enreg_id=(select enreg_id FROM &erp.met_indivart f WHERE f.client_cod=tf.client_cod AND f.individu_cod=tf.individu_cod AND f.refe_cod=tf.refe_cod AND f.refe_ind=tf.refe_ind) ;
 UPDATE se_met_indivart tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;

/*supprime des doublons de PK*/
DELETE se_met_indivart ia WHERE EXISTS
   ( SELECT 1 FROM
        (SELECT  client_cod, individu_cod, refe_cod, refe_ind,Count(*) FROM se_met_indivart
        GROUP BY client_cod, individu_cod, refe_cod, refe_ind
        HAVING Count(*)>1) doubles
    WHERE doubles.refe_cod=ia.refe_cod AND doubles.refe_ind=ia.refe_ind
      AND doubles.client_cod=ia.client_cod AND doubles.individu_cod=ia.individu_cod  ) ;


 CALL uvse_synchroverserp('client_cod@individu_cod@refe_cod@refe_ind','MET_INDIVART','O') ;

 commit;
 
 
select * from (
SELECT &outils.stragg(source) source,client_cod, individu_cod, refe_cod, refe_ind, enreg_id, combadv_id, gene_cod, gene_ind
FROM (
  SELECT 'uv' source ,g.* FROM se_met_indivart g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.met_indivart g
  )
GROUP BY client_cod, individu_cod, refe_cod, refe_ind, enreg_id, combadv_id, gene_cod, gene_ind
HAVING Count(1)!=2) r
ORDER BY client_cod, individu_cod, refe_cod, refe_ind ;